SELECT T.* ,
(SELECT COUNT(1)
FROM C1_NAVIGATION s
WHERE SYSDATE BETWEEN NVL(s.BEGIN_DATE, SYSDATE) AND NVL(s.END_DATE, SYSDATE)
AND s.PARENT_ID = t.id
) AS childTotal,
(select s.CODE FROM C1_NAVIGATION S WHERE S.ID = T.PARENT_ID) as PARENT_CODE
FROM C1_NAVIGATION T
JOIN c1_site t_site
ON (t_site.id = t.SITE_ID)
JOIN c1_dictionary t_dictionary
ON (t_dictionary.id = t.type_id )
WHERE SYSDATE BETWEEN NVL(t.BEGIN_DATE, SYSDATE) AND NVL(t.END_DATE, SYSDATE)
AND t_site.code = :siteCode
AND t_dictionary.code = :typeCode
ORDER BY t.ORDER_NUM, t.CODE